import collections
import datetime
import time
from django.shortcuts import render
from django.http import JsonResponse
from web import models
from django.db.models import Count


def dashboard(request, project_id):
    """问题状态数目分组展示"""
    # 根据status分组，获取每个组的数目
    issues_status_dict = collections.OrderedDict()  # 为状态创建有序字典
    for key, text in models.Issues.status_choices:
        issues_status_dict[key] = {'text': text, 'count': 0}  # 初始化字典
    issues_data = models.Issues.objects.filter(project_id=project_id).values('status').annotate(ct=Count('id'))
    # 获取 每个状态对应的问题数目
    for item in issues_data:
        issues_status_dict[item['status']]['count'] = item['ct']

    """项目成员"""
    user_list = models.ProjectUser.objects.filter(project_id=project_id).values('user_id', 'user__username')

    """动态"""
    # 获取最新10个问题动态
    top_ten = models.Issues.objects.filter(project_id=project_id, assign__isnull=False).order_by('-id')[0:10]
    return render(request, 'dashboard/dashboard.html',
                  {
                      'issues_status_dict': issues_status_dict,
                      'user_list': user_list,
                      'top_ten_obj': top_ten
                  })


# 问题图表展示
def issues_chart(request, project_id):
    """
    在概览页面生成highcharts所需的数据
    data:[datetime（x轴日期）,num（y轴问题个数）]
    """
    # 获取 30天内，每天的问题数
    today = datetime.datetime.now().date() # 获取今天的日期
    date_dict = collections.OrderedDict() # 构造字典
    # [('2021-08-24', [1629734400000.0, 0]),
    # ('2021-08-23', [1629648000000.0, 0])]
    for i in range(0, 30):
        date = today - datetime.timedelta(days=i)
        date_dict[date.strftime("%Y-%m-%d")] = [time.mktime(date.timetuple()) * 1000, 0]

    # select xxxx,1 as ctime from xxxx
    # select id,name,email from table;
    # select id,name, strftime("%Y-%m-%d",create_datetime) as ctime from table;
    # "DATE_FORMAT(web_transaction.create_datetime,'%%Y-%%m-%%d')"  MYSQL的格式化
    result = models.Issues.objects.filter(project_id=project_id,
                                          create_datetime__gte=today - datetime.timedelta(days=30)).extra(
        select={'ctime': "DATE_FORMAT(web_issues.create_datetime,'%%Y-%%m-%%d')"}).values('ctime').annotate(ct=Count('id'))

    for item in result:
        date_dict[item['ctime']][1] = item['ct']

    return JsonResponse({'status': True, 'data': list(date_dict.values())})
